﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Text;
using System.Data.SqlClient;

namespace ncp.Business
{
    public class ClientInfoBll
    {
        /// <summary>
        /// 得到用户信息表
        /// </summary>
        public static DataTable GetClientInfoTable(string p_Where, string p_Orderby, string pDataBase)
        {
            string sqlStr = "select *,Datediff(dd,Vip_EndDate,getdate()) as isVip from bi_ClientInfo";
            if (!string.IsNullOrEmpty(p_Where))
                sqlStr += " where " + p_Where;
            if (!string.IsNullOrEmpty(p_Orderby))
                sqlStr += " order by " + p_Orderby;
            return CommonBll.GetdtList(sqlStr, pDataBase);
        }

        public static DataTable GetClientInfoTable(int piPageIndex, int piPageSize, string pcFieldList, string pcOrderBy, string pcWhere, string pDatabase)
        {
            string sqlStr = CommonBll.GetPagingSQL(piPageIndex, piPageSize, "bi_ClientInfo", "*,Datediff(dd,Vip_EndDate,getdate()) as isVip,(select top 1 Points_Name from dbo.bi_PointRatio where Points_Value>User_Points order by Points_Value) as Points_Name", "Client_id", pcOrderBy, pcWhere);
            return CommonBll.GetdtList(sqlStr, pDatabase);
        }

        /// <summary>
        /// 得到分页用户信息表
        /// </summary>
        public static DataTable GetClientInfoTable(int piPageIndex, int piPageSize, string pcFieldList, string pcOrderBy, Dictionary<string, object> dicWhere, string pDataBase)
        {
            List<SqlParameter> ilist = new List<SqlParameter>();
            string pcWhere = "1=1 ";
            foreach (KeyValuePair<string, object> kvp in dicWhere)
            {
                if (kvp.Key != "more")
                {
                    pcWhere += " and " + kvp.Key + "=@" + kvp.Key;
                    ilist.Add(new SqlParameter("@" + kvp.Key, kvp.Value));
                }
                else
                {
                    pcWhere += " and " + kvp.Value;
                }
            }
            string sqlStr = CommonBll.GetPagingSQL(piPageIndex, piPageSize, "bi_ProductInfo_v", pcFieldList, "Product_Id", pcOrderBy, pcWhere);
            SqlParameter[] paras = ilist.ToArray();
            return CommonBll.GetdtList(sqlStr, pDataBase, paras);
        }

        /// <summary>
        /// 得到用户信息表
        /// </summary>
        public static DataTable GetClientInfoTable(string fieldList, Dictionary<string, object> dicWhere, string pDataBase)
        {
            List<SqlParameter> ilist = new List<SqlParameter>();
            string pcWhere = "1=1 ";
            foreach (KeyValuePair<string, object> kvp in dicWhere)
            {
                if (kvp.Key != "more")
                {
                    pcWhere += " and " + kvp.Key + "=@" + kvp.Key;
                    ilist.Add(new SqlParameter("@" + kvp.Key, kvp.Value));
                }
                else
                {
                    pcWhere += " and " + kvp.Value;
                }
            }
            string sqlStr = "select " + fieldList + " from bi_ClientInfo where " + pcWhere;
            SqlParameter[] paras = ilist.ToArray();
            return CommonBll.GetdtList(sqlStr, pDataBase, paras);
        }

        /// <summary>
        /// 得到记录数
        /// </summary>
        public static int GetClientInfoCount(string pcWhere, string pDataBase)
        {
            string sqlStr = "select count(*) from bi_ClientInfo";
            if (!string.IsNullOrEmpty(pcWhere))
                sqlStr += " where " + pcWhere;
            return CommonBll.ReadInteger(sqlStr, pDataBase);
        }


        /// <summary>
        /// 用户是否存在
        /// </summary>
        public static int IsExistClient(string Client_Account, string Client_id, string pDataBase)
        {
            string sqlStr = "select count(1) from bi_ClientInfo where Client_Account='" + Client_Account + "' and Client_id<>'" + Client_id + "'";
            return CommonBll.ReadInteger(sqlStr, pDataBase);
        }
        /// <summary>
        /// 用户是否存在
        /// </summary>
        public static DataTable GetClientById(string Client_id, string pDataBase)
        {
            string sqlStr = "select * from bi_ClientInfo where Client_id=@clientId";
            SqlParameter[] paras = new SqlParameter[]
                                       {
                                           new SqlParameter("@clientId", Client_id)
                                       };
            return CommonBll.GetdtList(sqlStr, pDataBase, paras);
        }

        /// <summary>
        /// 修改用户密码
        /// </summary>
        public static int UpdateClientPassword(string Client_Id, string Client_Password, string pDataBase)
        {
            string sqlStr = "update bi_ClientInfo set Client_Password='" + Client_Password.Replace("'", "''") + "' where Client_Id='" + Client_Id + "'";
            return CommonBll.ExecuteNonQuery(sqlStr, pDataBase);
        }

        /// <summary>
        /// 修改用户用户级别(0铁/1铜牌/2银牌/3金牌/4钻石)
        /// </summary>
        public static int UpdateClientClass(string Client_Id, string Client_Class, string pDataBase)
        {
            string sqlStr = "update bi_ClientInfo set Client_Class='" + Client_Class + "' where Client_Id='" + Client_Id + "'";
            return CommonBll.ExecuteNonQuery(sqlStr, pDataBase);
        }

        /// <summary>
        /// 修改用户用户积分和等级
        /// </summary>
        public static int UpdateClientPoints(string Client_Id, int Client_Points,string sign, string pDataBase)
        {
            string sqlStr =
                "update bi_ClientInfo set User_Points=User_Points" + sign + "@points,User_Class=(case when User_Points" + sign + "@points<500 then 0 when User_Points" + sign + "@points>=500 and User_Points" + sign + "@points<1000 then 1 when User_Points" + sign + "@points>=1000 and User_Points" + sign + "@points<2000 then 2 when User_Points" + sign + "@points>=2000 and User_Points" + sign + "@points<4000 then 3 else 4 end) where Client_Id=@clientId";
            SqlParameter[] paras = new SqlParameter[]
                                       {
                                           new SqlParameter("@clientId", Client_Id),
                                           new SqlParameter("@points", Client_Points)
                                       };
            return CommonBll.ExecuteNonQuery(CommandType.Text, sqlStr, pDataBase, paras);
        }

        //根据用户积分判断用户等级
        public static string GetClientLevel(string rating, string pDataBase)
        {
            string sqlStr = "select top 1 Points_name from dbo.bi_PointRatio where Points_Value>@rating  order by Points_Value";
            SqlParameter[] paras = new SqlParameter[]
                                       {
                                           new SqlParameter("@rating", rating)
                                       };
            var dt = CommonBll.GetdtList(sqlStr, pDataBase,paras);
            return dt.Rows[0]["Points_name"].ToString();
        }

        //根据用户积分判断用户头像
        public static string GetClientHeader(string rating, string pDataBase)
        {
            string sqlStr = "select top 1 Points_Picture from dbo.bi_PointRatio where Points_Value>@rating  order by Points_Value";
            SqlParameter[] paras = new SqlParameter[]
                                       {
                                           new SqlParameter("@rating", rating)
                                       };
            var dt = CommonBll.GetdtList(sqlStr, pDataBase, paras);
            return dt.Rows[0]["Points_Picture"].ToString();
        }

        /// <summary>
        /// 修改用户用户状态
        /// </summary>
        public static int UpdateClientStatus(string Client_Id, string User_Status, string pDataBase)
        {
            string sqlStr = "update bi_ClientInfo set User_Status='" + User_Status + "' where Client_Id='" + Client_Id + "'";
            return CommonBll.ExecuteNonQuery(sqlStr, pDataBase);
        }

        /// <summary>
        /// 修改用户信息
        /// </summary>
        public static int UpdateClientInfo(Dictionary<string, object> dicClient, string pDataBase)
        {
            string sqlStr = "UPDATE dbo.bi_ClientInfo " +
                            "SET Client_Name = '" + dicClient["client_name"].ToString() + "',	" +
                            "Client_Email = '" + dicClient["client_email"].ToString() + "', " +
                            "Client_CellPhone =  '" + dicClient["client_cellphone"].ToString() + "', " +
                            "Client_TelPhone =  '" + dicClient["client_telphone"].ToString() + "'," +
                            "Client_Contact = '" + dicClient["client_contact"].ToString() + "'," +
                            "Client_Url ='" + dicClient["client_url"].ToString() + "'," +
                            "Client_Address ='" + dicClient["client_address"].ToString() + "'," +
                            "Client_Type =  '" + dicClient["client_type"].ToString() + "'," +
                            "Client_Remark =  '" + dicClient["client_remark"].ToString() + "' " +
                            "where Client_id='" + dicClient["client_id"].ToString() + "'";
            return CommonBll.ExecuteNonQuery(sqlStr, pDataBase);
        }

        /// <summary>
        /// 增加用户信息
        /// </summary>
        public static int InsertClientInfo(Dictionary<string, object> dicClient, string pDataBase)
        {
            string sqlStr = "INSERT INTO dbo.bi_ClientInfo (Client_Account, Client_Name, Client_Password, Client_Email, Client_Type, Client_Class, Client_Points, Client_RegsiterDate, Client_Status, Client_Remark) " +
                            "VALUES ('" + dicClient["client_account"].ToString() + "', '" + dicClient["client_name"].ToString() + "', '" + dicClient["client_password"].ToString() + "', '" + dicClient["client_email"].ToString() + "', '" + dicClient["client_type"].ToString() + "','" + dicClient["client_class"].ToString() + "', '" + dicClient["client_points"].ToString() + "',getdate(), '" + dicClient["client_status"].ToString() + "', '" + dicClient["client_remark"].ToString() + "')";
            return CommonBll.ExecuteQuery(sqlStr, pDataBase);
        }

        /// <summary>
        /// 删除用户
        /// </summary>
        public static int DeleteClientInfo(string Client_id, string pDataBase)
        {
            string sqlStr = "delete from bi_ClientInfo where Client_id in(" + Client_id + ")";
            return CommonBll.ExecuteNonQuery(sqlStr, pDataBase);
        }
        /// <summary>
        /// 用户登录
        /// </summary>
        /// <param name="account"></param>
        /// <param name="password"></param>
        /// <param name="pDataBase"> </param>
        /// <returns></returns>
        public static DataTable ClientLogin(string account, string password, string pDataBase)
        {
            string sqlStr = "select * from bi_ClientInfo where Client_Account=@account and User_Password=@password and User_Status=1";
            SqlParameter[] paras = new SqlParameter[]
                                       {
                                           new SqlParameter("@account", account),
                                           new SqlParameter("@password", password)
                                       };
            return CommonBll.GetdtList(sqlStr, pDataBase,paras);
        }

        /// <summary>
        /// 用户注册
        /// </summary>
        public static int Register(string account, string pwd, string pDataBase)
        {
            string sql1 = "select count(*) from bi_ClientInfo where Client_Account='" + account + "'";
            if (CommonBll.ReadInteger(sql1, pDataBase)>0)
            {
                return -1;
            }
            StringBuilder builder=new StringBuilder();
            builder.Append("insert into bi_ClientInfo(Client_Account,User_Password,User_Status,User_RegsiterDate,User_Points,User_Class) values(");
            builder.AppendFormat("'{0}','{1}','{2}','{3}','{4}','{5}')", account, pwd, 1, DateTime.Now, CommonConst.gDefaultPoints, 0);
            if(CommonBll.ExecuteQuery(builder.ToString(), pDataBase)>0)
            {
                return 1;
            }
            return 0;
        }

        ///<summary>
        /// 修改用户信息
        /// </summary>
        public static int UserInfoModify(Dictionary<string, object> dic,string userId,string pDataBase)
        {
            string sql = "";
            
            if (dic["pwd"].ToString()=="")
            {
                sql = "update bi_ClientInfo set Client_Name=@name ,User_Email=@email , User_CellPhone=@cellphoe ,User_TelPhone=@telphone , User_Contact=@contact , User_Url=@url , User_Address=@addr,User_QQ=@qq where Client_Id=@userId";
            }
            else
            {
                sql = "update bi_ClientInfo set Client_Name=@name ,User_Password=@pwd , User_Email=@email , User_CellPhone=@cellphoe ,User_TelPhone=@telphone , User_Contact=@contact , User_Url=@url , User_Address=@addr,User_QQ=@qq where Client_Id=@userId";
            }
            SqlParameter[] paras = new SqlParameter[]
                                       {
                                           new SqlParameter("@name", dic["username"]),
                                           new SqlParameter("@email", dic["email"]),
                                           new SqlParameter("@cellphoe", dic["cellphone"]),
                                           new SqlParameter("@telphone", dic["telephone"]),
                                           new SqlParameter("@contact", dic["contact"]),
                                           new SqlParameter("@url", dic["compweb"]),
                                           new SqlParameter("@addr", dic["compaddr"]),
                                           new SqlParameter("@pwd", dic["pwd"]),
                                           new SqlParameter("@userId", userId),
                                           new SqlParameter("@qq", dic["qq"])
                                       };
            return CommonBll.ExecuteNonQuery(CommandType.Text, sql, pDataBase, paras);

        }

        ///<summary>
        /// 保存用户简介
        /// </summary>
        public static int SaveAboutUs(string content, string userId, string pDataBase)
        {
            string sql = "update bi_ClientInfo set User_Remark=@content where Client_Id=@userId";
            SqlParameter[] paras = new SqlParameter[]
                                       {
                                           new SqlParameter("@content", content),
                                           new SqlParameter("@userId", userId)
                                       };
            return CommonBll.ExecuteNonQuery(CommandType.Text, sql, pDataBase, paras);
        }

        /// <summary>
        /// 更新最后登录时间
        /// </summary>
        public static int UpateLastLoginTime(string clientId,string pDataBase)
        {
            string sql = "update bi_ClientInfo set LastLoginTime=getdate()";
            return CommonBll.ExecuteNonQuery(sql, pDataBase);
        }

        /// <summary>
        /// 更新自定义属性
        /// </summary>
        public static int UpateUser_Flag(string clientId, string pFlag, string pDataBase)
        {
            string sql = "update bi_ClientInfo set User_Flag= '" + pFlag + "' where Client_Id='" + clientId + "';";
            return CommonBll.ExecuteNonQuery(sql, pDataBase);
        }

        /// <summary>
        /// 升级为VIP
        /// </summary>
        public static int UpdateClientVip(Dictionary<string, object> dic, string pDataBase)
        {
            string sql = "update bi_ClientInfo set Vip_EndDate='" + dic["vip_enddate"].ToString() + "' where Client_Id='" + dic["client_id"].ToString() + "';";
            sql += "insert into bi_VipInfo(Client_Id,Vip_StartDate,Vip_EndDate,Vip_Remark) values('" + dic["client_id"].ToString() + "','" + dic["vip_startdate"].ToString() + "','" + dic["vip_enddate"].ToString() + "','" + dic["vip_remark"].ToString() + "');";
            return CommonBll.ExecuteTransaction(sql, pDataBase);
        }

        /// <summary>
        /// 得到VIP信息表
        /// </summary>
        public static DataTable GetVIPInfoTable(string p_Where, string p_Orderby, string pDataBase)
        {
            string sqlStr = "select *,Datediff(dd,Vip_EndDate,getdate()) as isVip from bi_VipInfo_v";
            if (!string.IsNullOrEmpty(p_Where))
                sqlStr += " where " + p_Where;
            if (!string.IsNullOrEmpty(p_Orderby))
                sqlStr += " order by " + p_Orderby;
            return CommonBll.GetdtList(sqlStr, pDataBase);
        }
    }
}
